package cn.com.open.itembank.mapper;

import cn.com.open.itembank.dto.QuestionCountModel;
import cn.com.open.itembank.entity.CourseItemBank;
import cn.com.open.itembank.sqlprovider.CourseItemBankSqlProvider;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * Created by lucky on 2017/7/13.
 */
@Mapper
public interface CourseItemBankMapper {

    //region 获取单条题库信息

    /**
     * 根据题库id获取可用题库信息.
     *
     * @param id 题库id
     * @return 可用题库信息 enabled by id
     */
    @Select("SELECT * FROM CourseItemBank WHERE ItemBankID = #{id} AND Status = 0")
    CourseItemBank getEnabledById(@Param("id") String id);

    /**
     * 根据资源课程id获取可用题库信息
     *
     * @param cid 资源课程id
     * @return 可用题库信息 enabled by course id
     */
    @Select("SELECT * FROM CourseItemBank WHERE CourseID = #{cid} AND Status = 0")
    CourseItemBank getEnabledByCourseId(@Param("cid") String cid);

    /**
     * 根据题库id获取未删除题库信息.
     *
     * @param id the id
     * @return 题库信息 not deleted by id
     */
    @Select("SELECT * FROM CourseItemBank WHERE ItemBankID = #{id} AND Status <>-1")
    CourseItemBank getNotDeletedById(@Param("id") String id);
    //endregion

    //region 判断题库是否存在

    /**
     * 根据appKey,机构ID和题库名称，判断是否存在数据.
     *
     * @param appKey appKey
     * @param orgid  机构ID
     * @param name   题库名称
     * @param id     题库ID
     * @return the int
     */
    @SelectProvider(type = CourseItemBankSqlProvider.class, method = "isExistCourseItemBankForInner")
    int isExistCourseItemBankForInner(@Param("appKey") String appKey, @Param("orgid") String orgid, @Param("name") String name, @Param("id") String id);


    /**
     * 根据appKey,资源课程ID判断是否存在数据.
     *
     * @param appKey appKey
     * @param cid    资源课程ID
     * @return the course item bank
     */
    @Select("SELECT COUNT(*) FROM CourseItemBank WHERE AppID = #{appKey} AND CourseID  = #{cid} AND Source=1")
    int isExistCourseItemBankForOuter(@Param("appKey") String appKey, @Param("cid") String cid);
    //endregion

    //region 新增修改删除

    /**
     * Insert 题库信息.
     *
     * @param model 题库信息
     * @return the int
     */
    @Insert("INSERT INTO CourseItemBank (ItemBankID,CourseID,CourseName,Status,Source,AppID,IsPublic,CreateTime,CreateOrgID) VALUES (#{itemBankID},#{courseID},#{courseName},#{status},#{source},#{appID},#{isPublic},#{createTime},#{createOrgID})")
    int insert(CourseItemBank model);

    /**
     * 删除题库.
     *
     * @param id 题库id
     * @return the int
     */
    @Delete("DELETE FROM CourseItemBank WHERE ItemBankID=#{id}")
    int delete(@Param("id") String id);


    /**
     * 修改题库名称.
     *
     * @param id         题库id
     * @param courseName 题库名称
     * @return the int
     */
    @Update("UPDATE CourseItemBank SET CourseName=#{courseName} WHERE ItemBankID=#{id}")
    int update(@Param("id") String id, @Param("courseName") String courseName);

    /**
     * 修改题库状态.
     *
     * @param id     题库id
     * @param status 题库新状态
     * @return the int
     */
    @Update("UPDATE CourseItemBank SET Status=#{status} WHERE ItemBankID=#{id}")
    int changeStatus(@Param("id") String id, @Param("status") int status);
    //endregion

    //region 根据题库ID获取各类相关资源数量，用于删除判断

    /**
     * 根据题库id获取试题类型关系数量.
     *
     * @param itemBankId the parent chapter id
     * @return 试题数量 question type count by item bank id
     */
    @Select("SELECT COUNT(*) FROM QuestionTypeUseRelation WHERE ItemBankID = #{itemBankId} AND Status <> -1")
    int getQuestionTypeCountByItemBankId(@Param("itemBankId") String itemBankId);

    /**
     * 根据题库id获取试题数量.
     *
     * @param itemBankId the parent chapter id
     * @return 试题数量 question count by item bank id
     */
    @Select("SELECT COUNT(*) FROM QuestionUseRelation WHERE ItemBankID = #{itemBankId}")
    int getQuestionCountByItemBankId(@Param("itemBankId") String itemBankId);

    /**
     * 根据题库id获取试卷数量.
     *
     * @param itemBankId the parent chapter id
     * @return 试卷数量 test paper count by item bank id
     */
    @Select("SELECT COUNT(*) FROM TestPaper WHERE CourseID = #{itemBankId} AND State <> -1")
    int getTestPaperCountByItemBankId(@Param("itemBankId") String itemBankId);

    /**
     * 根据题库id获取策略数量.
     *
     * @param itemBankId the parent chapter id
     * @return 策略数量 rules count by item bank id
     */
    @Select("SELECT COUNT(*) FROM Rules WHERE CourseID = #{itemBankId}")
    int getRulesCountByItemBankId(@Param("itemBankId") String itemBankId);


    /**
     * 根据题库id获取章节数量
     *
     * @param itemBankId the parent chapter id
     * @return 策略数量 rules count by item bank id
     */
    @Select("SELECT COUNT(*) FROM Chapter WHERE CourseID = #{itemBankId}")
    int getChapterByItemBankId(@Param("itemBankId") String itemBankId);

    //endregion


    /**
     * 查询题总数集合
     * @return
     */
    @Select("select \n" +
            "c.CourseID as courseID, \n" +
            "(\n" +
            " select count(1)\n" +
            " from Question as q\n" +
            " join QuestionUseRelation as re on re.QuestionID=q.QuestionID\n" +
            " where q.Status in (0,1) and re.Status in (0,1) and re.ItemBankID=c.ItemBankID\n" +
            ") as questionCount\n" +
            " from CourseItemBank as c\n" +
            " where c.Status=0\n" +
            " group by c.ItemBankID, c.CourseID, c.CourseName, c.AppID")
    List<QuestionCountModel> findQuestionCount();
}
